<?php
/**
 * RedCross Application
 *
 * @copyright Copyright (c) 2010, Kristian Stokke Tryggestad, Rune Vikestad, Andreas Flaten Wist, Simen Nicolai Echholt, Ole Henrik Jahren
 */

/**
 *  Gateway for the shifts
 *
 *  @category     RedCross
 *  @package    Core_Gateway
 */
class Core_Gateway_Shift extends RedCross_Gateway_GatewayAbstract
{

    public function __construct()
    {
        $this->_class = 'Shift';
        $this->_modelPath = 'Core_Model';
    }

    public function fetchMembersWithCourses($role_id)
    {
        $result = Doctrine_Query::create()
            ->select('m.id, m.first_name, m.last_name, (SELECT COUNT(*) FROM RedCross_Gateway_Base_RoleCourse rc WHERE rc.role_id = '
            . Doctrine_Manager::getInstance()->getCurrentConnection()->quoteIdentifier($role_id) . ') AS courses')
              ->from('Core_Model_Member m')
             ->leftJoin('m.Courses c')
             ->addWhere('c.id IN (SELECT rc2.course_id FROM RedCross_Gateway_Base_RoleCourse rc2 WHERE rc2.role_id = ?)', $role_id)
            ->groupBy('m.id')
            ->having('COUNT(*) = courses')
            ->fetchArray();

            return $result;
    }

    public function fetchByIdIncludingMembers($id)
    {
        $result = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' s')
            ->leftJoin('s.Roles r')
            ->leftJoin('r.Role ro')
            ->leftJoin('r.Member m')
            ->where($this->getPrimaryKey() . ' = ?', $id)
            ->fetchOne();

        return $result;
    }

    public function fetchAll($paginate = false)
    {
        $query = Doctrine_Query::create()
            ->select('s.*, e.name')
            ->from($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->orderBy('e.id DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->fetchArray();
    }

    public function fetchAllActive($paginate = false)
    {
        $date = new RedCross_Date();

        $query = Doctrine_Query::create()
            ->select('s.*, e.name')
            ->from($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->addWhere('s.to_date > ?', $date->export())
            ->orderBy('e.id DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->fetchArray();
    }

    public function fetchAllInActive($paginate = false)
    {
        $date = new RedCross_Date();

        $query = Doctrine_Query::create()
            ->select('s.*, e.name')
            ->from($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->addWhere('s.to_date < ?', $date->export())
            ->orderBy('e.id DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->fetchArray();
    }

    public function fetchAllContainingUnconfirmedShiftNotifications($paginate = false)
    {
        $date = new RedCross_Date();

        $query = Doctrine_Query::create()
            ->select('s.*, e.name')
            ->addSelect('COUNT(sr.id) unconfirmed')
            ->addFrom($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->addFrom('Core_Model_ShiftRole sr')
            ->addWhere('s.id = sr.shift_id')
            ->addWhere('sr.notification_sent_time IS NOT NULL')
            ->addWhere('sr.notification_confirmed_time IS NULL')
            ->addWhere('s.to_date > ?', $date->export())
            ->groupBy('s.id')
            ->having('unconfirmed > 0')
            ->orderBy('e.id DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->fetchArray();
    }

    public function fetchAllFutureWithoutFilledRoles($paginate = false)
    {
        $date = new RedCross_Date();

        $query = Doctrine_Query::create()
            ->select('s.*, e.name')
            ->addFrom($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->addFrom('Core_Model_ShiftRole sr')
            ->addWhere('s.id = sr.shift_id')
            ->addWhere('sr.member_id IS NULL')
            ->addWhere('s.from_date > ?', $date->export())
            ->groupBy('s.id')
            ->orderBy('e.id DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->fetchArray();
    }

    public function fetchAllPastWithoutShiftReport($paginate = false)
    {
        $date = new RedCross_Date();

        $query = Doctrine_Query::create()
            ->select('s.*, e.name')
            ->addFrom($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->addFrom('Core_Model_ShiftRole sr')
            ->addWhere('s.id = sr.shift_id')
            ->addWhere('s.shift_report_id IS NULL')
            ->addWhere('s.to_date < ?', $date->export())
            ->groupBy('s.id')
            ->orderBy('e.id DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->fetchArray();
    }

    public function fetchAllWithUnconfirmedShiftReport($paginate = false)
    {
        $query = Doctrine_Query::create()
            ->select('s.*, e.name')
            ->addFrom($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->leftJoin('s.ShiftReport srep')
            ->addWhere('srep.approved = 0')
            ->addFrom('Core_Model_ShiftRole sr')
            ->addWhere('s.id = sr.shift_id')
            ->groupBy('s.id')
            ->orderBy('e.id DESC');
        
        return $paginate ? new RedCross_Paginator($query) : $query->fetchArray();
    }

    /**
     * Fetches all shifts where a member has a role and the shift notification 
     * has been sent, but the member hasn't confirmed it yet.
     * @param int $memberId
     * @return array
     */
    public function fetchAllUnconfirmedForMember($memberId) {
        $date = new RedCross_Date();
        $dateNow = $date->export();

        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Shift s')
            ->leftJoin('s.Event e')
            ->leftJoin('s.Roles r')
            ->leftJoin('s.ShiftReport sr')
            ->leftJoin('r.Member m')
            ->leftJoin('r.Role r2')
            ->where('r.notification_sent_time IS NOT NULL AND m.id = ?', $memberId)
            ->andWhere('r.notification_confirmed_time IS NULL AND s.from_date > ?', $dateNow)
            ->orderBy('s.appearance_time DESC');

        return $query->execute();
    }
    
    /**
     * Fetches all shifts where a member has a role and the shift notification has been sent out.
     * Filters the list depending on the parameter $filterData
     * @param int $member_id
     * @param array $data
     * @return array
     */
    public function fetchAllVisibleForMember($member_id, $data = null, $paginate = false)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Shift s')
            ->leftJoin('s.Event e')
            ->leftJoin('s.Roles r')
            ->leftJoin('s.ShiftReport sr')
            ->leftJoin('r.Member m')
            ->leftJoin('r.Role r2')
            ->where('r.notification_sent_time IS NOT NULL AND m.id = ?', $member_id);

        if (isset($data['state']) && $data['state'] !== Core_ShiftController::ALL_SHIFTS)
        {
            $date = new RedCross_Date();
            $dateNow = $date->export();
            
            switch ((int) $data['state'])
            {
                case Core_ShiftController::FUTURE_SHIFTS_NOT_CONFIRMED:
                    $query->addWhere('r.notification_confirmed_time IS NULL AND s.from_date > ?', $dateNow);
                    break;
                case Core_ShiftController::FUTURE_SHIFTS_CONFIRMED:
                    $query->addWhere('r.notification_confirmed_time IS NOT NULL AND s.from_date > ?', $dateNow);
                    break;
                case Core_ShiftController::ALL_FUTURE_SHIFTS:
                    $query->addWhere('s.from_date > ?', $dateNow);
                    break;
                case Core_ShiftController::PREVIOUS_SHIFTS:
                    $date->add(1, RedCross_Date::DAY);
                    $query->addWhere('s.from_date < ?', $date->export());
                    break;
            }
            
        }

        if (isset($data['from_date']))
        {
            $query->addWhere('s.from_date >= ?', $data['from_date']);
        }
        if (isset($data['to_date']))
        {
            $query->addWhere('s.to_date <= ?', $data['to_date']);
        }

        $query->orderBy('s.appearance_time DESC');
        return $paginate ? new RedCross_Paginator($query) : $query->execute();
    }

    public function fetchAllMissingShiftReportsForMember($memberId) {
        $date = new RedCross_Date();
        $dateNow = $date->export();

        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Shift s')
            ->leftJoin('s.Event e')
            ->leftJoin('s.Roles r')
            ->leftJoin('s.ShiftReport sr')
            ->leftJoin('r.Member m')
            ->leftJoin('r.Role r2')
            ->where('r.notification_sent_time IS NOT NULL AND m.id = ?', $memberId)
            ->andWhere('s.from_date < ?', $dateNow)
            ->orderBy('s.appearance_time DESC');

        return $query->execute();
    }
    
    public function fetchByEventId($eventId)
    {
        $result = Doctrine_Query::create()
            ->select('*')
            ->from('Core_Model_Shift s')
            ->where('s.event_id = ?', $eventId)
            ->fetchArray();
        return $result;
    }
    
    public function fetchByIdJoinOnShiftReportShiftRolesRolesAndMember($shift_id)
    {
        $result = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' s')
            ->leftJoin('s.ShiftReport sr')    
            ->leftJoin('s.Roles r')
            ->leftJoin('r.Role ro')
            ->leftJoin('r.Member m')
            ->leftJoin('r.Attendant a')
            ->where($this->getPrimaryKey() . ' = ?', $shift_id)
            ->fetchOne();

        return $result;        
    }
    
    public function fetchFutureShifts($offset = null, $limit = null, $memberId = null)
    {
        $date = new RedCross_Date();

        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Shift s')
            ->leftJoin('s.Event e')
            ->leftJoin('s.Roles r')
            ->addWhere('s.from_date > ?', $date->export());

            if($memberId != null) {
                $query  ->addWhere('r.member_id = ?', $memberId)
                        ->addWhere('r.notification_sent_time IS NOT NULL');
            }

            $query->orderBy('s.appearance_time DESC');            

            if($offset != null && $offset !== 0){
                $query->offset($offset);
            }
            if($limit != null){
                $query->limit($limit);
            }
            
        return $query->fetchArray();        
    }
    
     public function fetchPastShifts($offset = null, $limit = null, $memberId = null)
     {
        
        $date = new RedCross_Date();

        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Shift s')
            ->leftJoin('s.Event e')
            ->leftJoin('s.Roles r')
            ->where('s.from_date < ?', $date->export())
            ->orderBy('s.appearance_time DESC');
            
            if($memberId != null) {
                $query   ->addWhere('r.member_id = ?', $memberId)
                         ->addWhere('r.notification_sent_time IS NOT NULL');
            }
        
            if($offset != null && $offset !== 0){
                $query->offset($offset);
            }
            if($limit != null){
                $query->limit($limit);
            }
            
        return $query->fetchArray();        
     }

    public function fetchByIdForViewShift($shiftId)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' s')
            ->leftJoin('s.Roles sr')
            ->leftJoin('s.ShiftReport srep')
            ->leftJoin('sr.Member m')
            ->leftJoin('sr.Role r')
            ->where($this->getPrimaryKey() . ' = ?', $shiftId);

        return $query->fetchOne();
    }

    public function fetchByIdForEditShift($shiftId)
    {
        $query = Doctrine_Query::create()
            ->select('s.*, e.*, sr.*, r.name, m.first_name, m.last_name')
            ->from($this->getModelClass() . ' s')
            ->leftJoin('s.Event e')
            ->leftJoin('s.Roles sr')
            ->leftJoin('sr.Role r')
            ->leftJoin('sr.Member m')
            ->where($this->getPrimaryKey() . ' = ?', $shiftId);

        return $query->fetchOne();
    }

}
